// Ofri Keidar 302933981 group 05

package controller;

import static org.junit.Assert.*;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import model.SystemDataManager;
import model.database.DBException;
import model.database.StoreDB;

import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;

import com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource;
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

import controller.SystemController;

/**
 * Class to test all of manager's transactions.
 * Class adds test record to database, and uses system's controller to run
 * the transactions and check the results.
 * IMPORTANT:
 * database must be empty before running tests, because expected values are
 * based on test records only
 */
public class ManagerControllerTester {
	
	/**
	 * Method that runs before the first test.
	 * This method enters to database the necessary test records.
	 * Test records are:
	 * 1. customer
	 * 2. user-name for customer
	 * 3. employee
	 * 4. user-name for employee
	 * 5. product
	 */	
	@BeforeClass
	public static void setup()  {

		// connection to database
		Connection connection = null;
		MysqlDataSource ds = new MysqlConnectionPoolDataSource();
		
    	// set server and database        	
    	ds.setServerName(TestCommons.SERVER_NAME);
    	ds.setDatabaseName(TestCommons.DB_NAME);

		try {
			
			connection = ds.getConnection(TestCommons.DB_USER, TestCommons.DB_PWD);
			Statement statement = connection.createStatement();
			
			// create a customer record
			// create query string
			String query = String
					.format("INSERT INTO %s (%s, %s, %s, %s, %s) VALUES ('%s', '%s', '%s', '%s', '%s')",
							TestCommons.CUSTOMERS_TABLE, TestCommons.NUMBER,
							TestCommons.NAME, TestCommons.USER_NAME, TestCommons.ADDRESS,
							TestCommons.PAYMENT_METHOD, TestCommons.TEST_NUMBER,
							TestCommons.TEST_NAME,
							TestCommons.TEST_CUSTOMER_USER_NAME,
							TestCommons.TEST_ADDRESS,
							TestCommons.TEST_PAYMENT_METHOD);

			// execute query
			if (statement.executeUpdate(query) == 0) {

				// could not add customer
				fail();
			}
			
			// set user-name for customer
			query = String.format(
					"INSERT INTO %s (%s, %s, %s) VALUES ('%s', '%s', '%s')",
					TestCommons.PASSWORDS_TABLE, TestCommons.USER_NAME,
					TestCommons.PASSWORD, TestCommons.PERMISSION,
					TestCommons.TEST_CUSTOMER_USER_NAME,
					TestCommons.TEST_PASSWORD,
					CommonValues.permissions.CUSTOMER.ordinal());
			
			// execute query
			if (statement.executeUpdate(query) == 0) {

				// could not add customer user-name
				fail();
			}
			
			// create an employee record
			// create query string
			query = String.format(
					"INSERT INTO %s (%s, %s, %s) VALUES ('%s', '%s', '%s')",
					TestCommons.EMPLOYEES_TABLE, TestCommons.NUMBER, TestCommons.NAME,
					TestCommons.USER_NAME, TestCommons.TEST_NUMBER,
					TestCommons.TEST_NAME, TestCommons.TEST_EMPLOYEE_USER_NAME);
			
			// execute query
			if (statement.executeUpdate(query) == 0) {

				// could not add employee
				fail();
			}
			
			// set user-name for employee
			query = String.format(
					"INSERT INTO %s (%s, %s, %s) VALUES ('%s', '%s', '%s')",
					TestCommons.PASSWORDS_TABLE, TestCommons.USER_NAME,
					TestCommons.PASSWORD, TestCommons.PERMISSION,
					TestCommons.TEST_EMPLOYEE_USER_NAME,
					TestCommons.TEST_PASSWORD,
					CommonValues.permissions.EMPLOYEE.ordinal());

			// execute query
			if (statement.executeUpdate(query) == 0) {

				// could not add product
				fail();
			}
			
			// create a product record
			// create query string
			query = String.format(
					"INSERT INTO %s (%s, %s) VALUES ('%s', '%s')",
					TestCommons.PRODUCTS_TABLE, TestCommons.NAME, TestCommons.PRICE,
					TestCommons.TEST_PRODUCT_NAME, TestCommons.TEST_PRODUCT_PRICE);

			// execute query
			if (statement.executeUpdate(query) == 0) {

				// could not add product
				fail();
			}

			// create order record
			// create query string
			query = String.format(
					"INSERT INTO %s (%s, %s, %s, %s, %s) VALUES (CURDATE(), '%s', '%s', '%s', '%f')",
					TestCommons.ORDERS_TABLE, TestCommons.DATE, TestCommons.ORDERING_CUSTOMER_NUMBER, TestCommons.NAME, 
					TestCommons.AMOUNT, TestCommons.TOTAL_SUM,
					TestCommons.TEST_NUMBER, TestCommons.TEST_PRODUCT_NAME, TestCommons.TEST_AMOUNT, 
					TestCommons.TEST_AMOUNT*TestCommons.TEST_PRODUCT_PRICE);
			
			// execute query
			if (statement.executeUpdate(query) == 0) {

				// could not add product
				fail();
			}
			
		} catch (SQLException e) {

			// check reason for error
			e.printStackTrace();
			
			// all tests should fails in case of exception
			fail();
			
		} finally {
			
			// close connection
			if (connection != null) {
				try {
					
					connection.close();
					
				} catch (SQLException e) {

					// check reason for error
					e.printStackTrace();
					
					// tests should fail in case of exception
					fail();
				}
			}
		}
	}

	/**
	 * Method that after the last test.
	 * This method deletes from database all test records
	 * that were added in setup()
	 */
	@AfterClass
	public static void teardown() {

		// connection to database
		Connection connection = null;
		MysqlDataSource ds = new MysqlConnectionPoolDataSource();

		// set server and database
		ds.setServerName(TestCommons.SERVER_NAME);
		ds.setDatabaseName(TestCommons.DB_NAME);

		try {
			
			// connect to database
			connection = ds.getConnection(TestCommons.DB_USER, TestCommons.DB_PWD);
			Statement statement = connection.createStatement();
			
			// delete test customer record
			String query = String.format("DELETE FROM %s WHERE %s = %s", 
					TestCommons.CUSTOMERS_TABLE, TestCommons.NUMBER, TestCommons.TEST_NUMBER);
			
			// execute query
			if (statement.executeUpdate(query) == 0) {

				// could not delete customer
				fail();
			}
			
			// delete test customer's user record
			query = String.format("DELETE FROM %s WHERE %s = '%s'", 
					TestCommons.PASSWORDS_TABLE, TestCommons.USER_NAME, TestCommons.TEST_CUSTOMER_USER_NAME);
			
			// execute query
			if (statement.executeUpdate(query) == 0) {

				// could not delete customer's user
				fail();
			}
			
			// delete test employee record
			query = String.format("DELETE FROM %s WHERE %s = %s", 
					TestCommons.EMPLOYEES_TABLE, TestCommons.NUMBER, TestCommons.TEST_NUMBER);
			
			// execute query
			if (statement.executeUpdate(query) == 0) {

				// could not delete employee
				fail();
			}
			
			// delete test employee's user record
			query = String.format("DELETE FROM %s WHERE %s = '%s'", 
					TestCommons.PASSWORDS_TABLE, TestCommons.USER_NAME, TestCommons.TEST_EMPLOYEE_USER_NAME);
			
			// execute query
			if (statement.executeUpdate(query) == 0) {

				// could not delete employee's user
				fail();
			}

			// delete test product record
			query = String.format("DELETE FROM %s WHERE %s = '%s' ", 
					TestCommons.PRODUCTS_TABLE, TestCommons.NAME, TestCommons.TEST_PRODUCT_NAME);
			
			// execute query
			if (statement.executeUpdate(query) == 0) {

				// could not delete product
				fail();
			}
			
			// delete test order record
			query = String.format("TRUNCATE TABLE %s", TestCommons.ORDERS_TABLE);
			
			// execute query
			statement.executeUpdate(query);
			
		} catch (SQLException e) {

			// check reason for error
			e.printStackTrace();
			
			// test should fail in case of exception
			fail();
			
		} finally {
			
			// close connection
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {

					// check reason for error
					e.printStackTrace();
					
					// tests should fail in case of exception
				}
			}
		}
		
	}
	
	/**
	 * Verifies that returned employee is test employee record
	 */
	@Test
	public void getEmployeesTest() {
		
		try {
			
			// create database
			StoreDB db = new StoreDB();
			
			// create model
			SystemDataManager dataManager = new SystemDataManager(db);
			
			// create controller
			SystemController controller = new SystemController(dataManager);
			
			// get all products			
			List<Map<String, String>> result  = controller.getEmployees();
			
			// build expected value
			List<Map<String, String>> expected = new LinkedList<Map<String,String>>();			
			Map<String, String> firstEmployee = new HashMap<String, String>();
			firstEmployee.put(CommonValues.NUMBER, ""+TestCommons.TEST_NUMBER);
			firstEmployee.put(CommonValues.NAME, TestCommons.TEST_NAME);
			firstEmployee.put(CommonValues.USER_NAME, TestCommons.TEST_EMPLOYEE_USER_NAME);
			expected.add(firstEmployee);
			
			// compare values
			assertEquals(expected, result);
			
		} catch (DBException e) {
			
			// check reason for error
			e.printStackTrace();
			
			// test should fail in case of exception
			fail();
		}		
	}

	/**
	 * Verifies that returned customer is test customer record
	 */
	@Test
	public void getCustomersTest() {
		
		try {
			
			// create database
			StoreDB db = new StoreDB();
			
			// create model
			SystemDataManager dataManager = new SystemDataManager(db);
			
			// create controller
			SystemController controller = new SystemController(dataManager);
			
			// get all customers			
			List<Map<String, String>> result  = controller.getCustomers();
			
			// build expected value
			List<Map<String, String>> expected = new LinkedList<Map<String,String>>();
			Map<String, String> firstCustomer = new HashMap<String, String>();
			firstCustomer.put(CommonValues.NUMBER, ""+TestCommons.TEST_NUMBER);
			firstCustomer.put(CommonValues.NAME, TestCommons.TEST_NAME);
			firstCustomer.put(CommonValues.USER_NAME, TestCommons.TEST_CUSTOMER_USER_NAME);
			firstCustomer.put(CommonValues.ADDRESS, TestCommons.TEST_ADDRESS);
			firstCustomer.put(CommonValues.PAYMENT_METHOD, 
					TestCommons.TEST_PAYMENT_METHOD);
			expected.add(firstCustomer);
			
			// compare values
			assertEquals(expected, result);
			
		} catch (DBException e) {
			
			// check reason for error
			e.printStackTrace();
			
			// test should fail in case of exception
			fail();
		}		
	}
	
	/**
	 * Asks for customers who purchased products in worth of at least
	 * test value. Verifies that returned customer is test customer record
	 */
	@Test
	public void getCustomersByExpensesTest() {
		
		try {
			
			// create database
			StoreDB db = new StoreDB();
			
			// create model
			SystemDataManager dataManager = new SystemDataManager(db);
			
			// create controller
			SystemController controller = new SystemController(dataManager);
			
			// get all customers by expenses
			List<Map<String, String>> result  = controller.getCustomers(TestCommons.TEST_PRODUCT_PRICE-1);
			
			// build expected value
			List<Map<String, String>> expected = new LinkedList<Map<String,String>>();
			Map<String, String> firstCustomer = new HashMap<String, String>();
			firstCustomer.put(CommonValues.NUMBER, ""+TestCommons.TEST_NUMBER);
			firstCustomer.put(CommonValues.NAME, TestCommons.TEST_NAME);
			firstCustomer.put(CommonValues.USER_NAME, TestCommons.TEST_CUSTOMER_USER_NAME);
			firstCustomer.put(CommonValues.ADDRESS, TestCommons.TEST_ADDRESS);
			firstCustomer.put(CommonValues.PAYMENT_METHOD, 
					TestCommons.TEST_PAYMENT_METHOD);
			expected.add(firstCustomer);
			
			// compare values
			assertEquals(expected, result);
			
		} catch (DBException e) {
			
			// check reason for error
			e.printStackTrace();
			
			// test should fail in case of exception
			fail();
		}		
	}
	
	/**
	 * Asks for customers who purchased test product. 
	 * Verifies that returned customer is test customer record
	 */
	@Test
	public void getCustomersByProductTest() {
		
		try {
			
			// create database
			StoreDB db = new StoreDB();
			
			// create model
			SystemDataManager dataManager = new SystemDataManager(db);
			
			// create controller
			SystemController controller = new SystemController(dataManager);
			
			// get all customers who but the products
			List<Map<String, String>> result  = controller.getCustomers(TestCommons.TEST_PRODUCT_NAME);
			
			// build expected value
			List<Map<String, String>> expected = new LinkedList<Map<String,String>>();
			Map<String, String> firstCustomer = new HashMap<String, String>();
			firstCustomer.put(CommonValues.NUMBER, ""+TestCommons.TEST_NUMBER);
			firstCustomer.put(CommonValues.NAME, TestCommons.TEST_NAME);
			firstCustomer.put(CommonValues.USER_NAME, TestCommons.TEST_CUSTOMER_USER_NAME);
			firstCustomer.put(CommonValues.ADDRESS, TestCommons.TEST_ADDRESS);
			firstCustomer.put(CommonValues.PAYMENT_METHOD, 
					TestCommons.TEST_PAYMENT_METHOD);
			expected.add(firstCustomer);
		
			// compare values
			assertEquals(expected, result);
			
		} catch (DBException e) {
			
			// check reason for error
			e.printStackTrace();
			
			// test should fail in case of exception
			fail();
		}		
	}
	
	/**
	 * Verifies that sales report for current date is
	 * test-amount times test-product-price
	 */
	@Test
	public void getSalesReportByDayTest() {
		
		try {
			
			// create database
			StoreDB db = new StoreDB();
			
			// create model
			SystemDataManager dataManager = new SystemDataManager(db);
			
			// create controller
			SystemController controller = new SystemController(dataManager);
			
			// get current date in format yyyy-mm-dd
			Calendar cal = Calendar.getInstance();
			SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
			String date = dateFormat.format(cal.getTime());
			
			// get report for current date			
			double report  = controller.getSalesReport(date);
			
			// build expected value (worth of test order)
			double expected = TestCommons.TEST_AMOUNT * TestCommons.TEST_PRODUCT_PRICE;
			
			// compare values
			assertEquals(expected, report, 0);
			
		} catch (DBException e) {
			
			// check reason for error
			e.printStackTrace();
			
			// test should fail in case of exception
			fail();
		}		
	}
	
	/**
	 * Verifies that sales report for current month is
	 * test-amount times test-product-price
	 */
	@Test
	public void getSalesReportByMonthTest() {
		
		try {
			
			// create database
			StoreDB db = new StoreDB();
			
			// create model
			SystemDataManager dataManager = new SystemDataManager(db);
			
			// create controller
			SystemController controller = new SystemController(dataManager);
			
			// get report for current month			
			double report  = controller.getSalesReport();

			// build expected value (worth of test order)
			double expected = TestCommons.TEST_AMOUNT * TestCommons.TEST_PRODUCT_PRICE;
			
			// compare values
			assertEquals(expected, report, 0);
			
		} catch (DBException e) {
			
			// check reason for error
			e.printStackTrace();
			
			// test should fail in case of exception
			fail();
		}		
	}
}